﻿using System;
using System.Collections.Generic;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;

namespace QWManage.MySqlSync
{
    public class SqlHelper
    {
        public static List<FieldInfo> GetFields()
        {
            List<FieldInfo> fieldInfos_all = new List<FieldInfo>();

            BindingFlags bindingFlags = BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance;

            FieldInfo[] fieldInfos;

            fieldInfos = typeof(MemberInfo).GetFields(bindingFlags);


            fieldInfos_all.AddRange(fieldInfos);

            return fieldInfos_all;
        }
        public static string GetUpdateKeyValues(int count = 0)
        {
            string keyValues = "";
            List<FieldInfo> fieldInfos = GetFields();
            int total = 0;
            foreach (FieldInfo fieldInfo in fieldInfos)
            {
                string name = Regex.Match(fieldInfo.Name, "<(?<name>.*?)>").Groups["name"].Value.Trim();

                if (name == "id") continue;

                keyValues += "[" + name + "]=@" + name + ",";
                total += 1;
                if (count > 0 && total == count) break;
            }
            return keyValues.TrimEnd(',');
        }

        public static string GetInsertNameColumns(int count = 0)
        {
            string columns = "";
            List<FieldInfo> fieldInfos = GetFields();
            int total = 0;
            foreach (FieldInfo fieldInfo in fieldInfos)
            {
                string name = Regex.Match(fieldInfo.Name, "<(?<name>.*?)>").Groups["name"].Value.Trim();
                columns += "[" + name + "],";
                total += 1;
                if (count > 0 && total == count) break;
            }
            return columns.TrimEnd(',');
        }

        public static string GetInsertValueColumns(int count = 0)
        {
            string columns = "";
            int total = 0;
            List<FieldInfo> fieldInfos = GetFields();
            foreach (FieldInfo fieldInfo in fieldInfos)
            {
                string name = Regex.Match(fieldInfo.Name, "<(?<name>.*?)>").Groups["name"].Value.Trim();


                columns += "@" + name + ",";
                total += 1;
                if (count > 0 && total == count) break;
            }
            return columns.TrimEnd(',');
        }

        public static string GetReader()
        {
            StringBuilder sb = new StringBuilder();
            List<FieldInfo> fieldInfos = GetFields();
            foreach (FieldInfo field in fieldInfos)
            {
                string name = Regex.Match(field.Name, "<(?<name>.*?)>").Groups["name"].Value.Trim();
                if (field.FieldType == typeof(string))
                    sb.AppendLine($"obj.{name}=ReadString(reader,\"{name}\");");

                else if (field.FieldType == typeof(int))
                    sb.AppendLine($"obj.{name}=ReadInt(reader,\"{name}\");");
                else if (field.FieldType == typeof(int?))
                    sb.AppendLine($"obj.{name}=ReadIntNull(reader,\"{name}\");");

                else if (field.FieldType == typeof(long))
                    sb.AppendLine($"obj.{name}=ReadLong(reader,\"{name}\");");
                else if (field.FieldType == typeof(long?))
                    sb.AppendLine($"obj.{name}=ReadLongNull(reader,\"{name}\");");

                else if (field.FieldType == typeof(DateTime))
                    sb.AppendLine($"obj.{name}=ReadDateTime(reader,\"{name}\");");
                else if (field.FieldType == typeof(DateTime?))
                    sb.AppendLine($"obj.{name}=ReadDateTimeNull(reader,\"{name}\");");

                else if (field.FieldType == typeof(decimal))
                    sb.AppendLine($"obj.{name}=ReadDecimal(reader,\"{name}\");");
                else if (field.FieldType == typeof(decimal?))
                    sb.AppendLine($"obj.{name}=ReadDecimalNull(reader,\"{name}\");");
                else
                    throw new Exception($"unexpected filed {field.Name}:{name}:{field.FieldType}");
            }
            string results = sb.ToString().Trim();
            return results;
        }
        public static string GetInsertParameters()
        {
            StringBuilder sb = new StringBuilder();
            List<FieldInfo> fieldInfos = GetFields();
            string prefix = "command.Parameters.AddWithValue";
            foreach (FieldInfo field in fieldInfos)
            {
                string name = Regex.Match(field.Name, "<(?<name>.*?)>").Groups["name"].Value.Trim();
                sb.AppendLine(prefix + $"(\"@{name}\", SqlNull(obj.{name}));");
            }
            string results = sb.ToString().Trim();
            return results;

        }
    }
}
